package uk.ac.sanger.dasregistry.timer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import java.util.logging.Logger;
import org.biojava.services.das.dao.DasSourceManager;

import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class CleanLogs implements Job{
	
	public static Logger logger = Logger
	.getLogger("uk.ac.sanger.dasregistry.timer.CleanLogs");
	private static final long serialVersionUID = -1397090078358304526L;
	
	private final static String DATASOURCE_NAME = "jdbc/mysql";
	
	
	private static final int PREVIOUS_START = -60;
	private static final int PREVIOUS_END   = 20;
	
	DasSourceManager dao;

	public void execute(JobExecutionContext arg0) throws JobExecutionException {

		
		logger.info("CleanLogs called");
		

		DataSource ds = getDataSource();

		Connection conn = null;

		try {

			conn = ds.getConnection();
			
			boolean doClean = dao.doAdminChecks(conn,"lastClean","cleanStarted","cleaning") ;
			
			if ( doClean) { 			
				cleanUptimeStats(conn);
				dao.releaseAdminLock(conn,"cleaning","lastClean");
			} else{
				logger.info("clean logs not run due to admin checks");
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null ) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		logger.info("finished cleanLogFileServlet...");
		
	
	}
	

	
	public CleanLogs(){
		
	}
	
	private DataSource getDataSource() {
		DataSource dataSource = null;
		try {
			Context env = (Context) new InitialContext().lookup("java:comp/env");
			dataSource = (DataSource) env.lookup(DATASOURCE_NAME);
		} catch ( Exception e) {
			e.printStackTrace();
		}   
		return dataSource;
	}
	
	private void cleanUptimeStats(Connection conn)
	throws SQLException{
		String timeorder = "select distinct(DATE_FORMAT(time,\"%Y-%m-%d\")) from uptime where (time > ?) and (time < ?) " ;
		PreparedStatement times = conn.prepareStatement(timeorder);
		
		String idPerDay = "select distinct(auto_id) from uptime where DATE_FORMAT(time,\"%Y-%m-%d\") = ?";
		PreparedStatement idPerDaySql = conn.prepareStatement(idPerDay);
		
		Date now = new Date();
		
		Calendar cal = new GregorianCalendar();
		cal.setTime(now);

		cal.add(Calendar.DAY_OF_MONTH, PREVIOUS_START);		
		long startTime = cal.getTime().getTime();
		
		cal.add(Calendar.DAY_OF_MONTH, PREVIOUS_END);		
		long endTime =  cal.getTime().getTime();
		
						
		java.sql.Date vold = new java.sql.Date(startTime);
		java.sql.Date old = new java.sql.Date(endTime);  
		
		logger.info( "cleaning the time range from " + vold + " - " + old);
		times.setDate(1,vold);
		times.setDate(2,old);
								
		ResultSet timesrs = times.executeQuery() ;
		
		while ( timesrs.next()){
			idPerDaySql.clearParameters();
	
			String daystr = timesrs.getString(1);
			logger.info("removing redundancy in logs on " + daystr);
			idPerDaySql.setString(1,daystr);
			ResultSet dayIds = idPerDaySql.executeQuery();
			while (dayIds.next()){
				int auto_id = dayIds.getInt(1);
				//System.out.println(auto_id);
				cleanServerDay(conn,auto_id,daystr);
			}
			dayIds.close();
			
		}	
		timesrs.close();
		idPerDaySql.close();
		times.close();
	}
	
	private void cleanServerDay(Connection conn, int auto_id, String daySqlStr)
	throws SQLException{
		
		//System.out.println(daySqlStr);
		String sql1 = "select distinct(status) from uptime where auto_id = ? and " +
		" DATE_FORMAT(time,\"%Y-%m-%d\")  = ?";
		
		PreparedStatement s1 = conn.prepareStatement(sql1);
		
		s1.setInt(1,auto_id);
		s1.setString(2,daySqlStr);
		
		ResultSet set = s1.executeQuery();
		
		boolean wasUp   = false;
		boolean wasDown = false;
		
		while (set.next()){
			int status = set.getInt(1);
			logger.fine("got status " + status + " for " + daySqlStr);
			if ( status == 1)
				wasUp = true;
			if ( status == 0)
				wasDown = true;
		}
		set.close();
		s1.close();
		
		// remove the old data;
		String remo = "delete from uptime where auto_id = ? and " +
		" DATE_FORMAT(time,\"%Y-%m-%d\")  = ?";

		PreparedStatement rsql = conn.prepareStatement(remo);
		rsql.setInt(1,auto_id);
		rsql.setString(2,daySqlStr);
		
		
		rsql.executeUpdate();
		
		
		// insert the statuses
		String is = "insert into uptime (auto_id, status, time) values(?,?,?)";
		PreparedStatement insertSql = conn.prepareStatement(is);
		
		if ( wasUp == true){
			storeUptime(insertSql, auto_id,1,daySqlStr);
		}
	
		if ( wasDown == true){
			storeUptime(insertSql, auto_id,0,daySqlStr);			
		}
		insertSql.close();
		rsql.close();
		
	}
	
	
	private void storeUptime(PreparedStatement insertSql,
			int auto_id,
			int status, 
			String dayString) 	
	throws SQLException {
		insertSql.clearParameters();
		insertSql.setInt(1, auto_id);
		insertSql.setInt(2, status);
		insertSql.setString(3,dayString );

		insertSql.executeUpdate();
	}
}
